/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package etc;

import com.mysql.jdbc.ResultSetMetaData;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Zoli
 */
public class Database
{
    private static Database instance=null;
    private static Connection connection=null;
    private String database,user,server,password;
    private ResultSet result;


    public Database()
    {

    }

    public void setDatabase(String database)
    {
        this.database=database;
    }

    public String getDatabase()
    {
        return this.database;
    }

    public void setUser(String user)
    {
        this.user=user;
    }

    public String getUser()
    {
        return this.user;
    }

    public void setServer(String server)
    {
        this.server=server;
    }

    public String getServer()
    {
        return this.server;
    }

    public void setPassword(char[] password)
    {
        this.password="";
        for (int i=0;i<password.length;i++)
        {
            this.password=this.password+password[i];
        }
    }

    private String getPassword()
    {
        return this.password;
    }

    public Database getInstance()
    {
        if (instance==null)
        {
            instance=new Database();
            return instance;
        }
        else
        {
            return instance;
        }
    }

    public int getColumnNumber()
    {
        ResultSetMetaData res = null;

        try
        {
            res = (ResultSetMetaData) this.result.getMetaData();
            return res.getColumnCount();


        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE );
            return 0;
        }


    }

    public Vector getColumnNames()
    {
        Vector v=new Vector(this.getColumnNumber());
        ResultSetMetaData md;
        try
        {
            md = (ResultSetMetaData) this.result.getMetaData();
            for (int i=1;i<=getColumnNumber();i++)
            {
                v.add(md.getColumnName(i));
            }
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE );
        }
        return v;
    }



    public boolean connect()
    {
      if (connection==null)
      {
            boolean sucess=false;

            try
            {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://" + this.server + "/" + this.database + "?user=" + this.user + "&password=" + this.password);
                sucess=true;
            }
            catch (ClassNotFoundException ex)
            {
                JOptionPane.showMessageDialog(null, ex.getMessage(),"Could not connect",JOptionPane.ERROR_MESSAGE);
            }
            catch (SQLException ex)
            {
                JOptionPane.showMessageDialog(null, ex.getMessage(),"Could not connect",JOptionPane.ERROR_MESSAGE);
            }
            finally
            {
                if (sucess)
                    return true;
                else
                    return false;
            }
      }
      return false;
    }

    public static void close()
    {
        try
        {
            connection.close();
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(),"Could not close connection",JOptionPane.ERROR_MESSAGE);
        }
    }

    public boolean update(String q)
    {
        Statement st;
        boolean ok=false;
        try
        {
            st= connection.createStatement();
            st.executeUpdate(q);
            ok=true;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE );
            ok=false;
        }
        finally
        {
            return ok;
        }

    }

    public void query(String q)
    {
        Statement st;

        try
        {
            st=connection.createStatement();
            this.result=st.executeQuery(q);

            if (st.execute(q))
            {
                this.result=st.getResultSet();
            }
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE );
        }
    }

    public DefaultTableModel getQueryResult()
    {
        Vector v=new Vector();
        DefaultTableModel table = null;
        boolean ok=false;
        Vector names=this.getColumnNames();
        int columns=this.getColumnNumber();

        try
        {
            while (this.result.next())
            {
                Vector row=new Vector();
                for (int i=0;i<columns;i++)
                {
                    row.add(this.result.getObject(names.get(i).toString()).toString());
                }
                v.add(row);
            }

            table=new DefaultTableModel(v,names);
            ok=true;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE );
            ok=false;
        }
        finally
        {
            if (!ok)
                return null;
            else
                return table;
        }
    }

    public Vector getTableNames()
    {

        Vector v = new Vector();
        boolean ok=false;

        try
        {
            DatabaseMetaData dbm = Database.connection.getMetaData();
            String[] names = {"TABLE"};
            ResultSet tables = dbm.getTables(null,"%", "%", names);

            while (tables.next())
            {
                v.add(tables.getString("TABLE_NAME"));
            }
            ok=true;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(null, ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            ok=false;
        }
        finally
        {
            if (!ok)
                return null;
            else
                return v;
        }
    }





}
